package com.class5.springboot.frame.repo.real.init;

import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.List;
import java.util.Map;

import com.class5.springboot.frame.repo.exception.RepositoryException;
import com.class5.springboot.frame.repo.tool.xml.DbTable;
import com.class5.springboot.frame.repo.tool.xml.DbTableColumn;
import org.springframework.jdbc.core.JdbcTemplate;


public class DbTracker {

    //保存数据库表最近更新的一些信息
    public final static String DB_INIT_TABLE = "db_inited";
    private final static String DB_INIT_TABLE_DDL = "CREATE TABLE "+DB_INIT_TABLE+"("
            + "id bigint unsigned NOT NULL PRIMARY KEY AUTO_INCREMENT COMMENT '编号', "
            + "init_key varchar(128) NOT NULL COMMENT '初始化块标识',"
            + "version text NOT NULL COMMENT '已初始化版本',"
            + "init_time datetime NOT NULL COMMENT '更新时间',"
            + "UNIQUE (init_key)"
            + ")DEFAULT CHARSET=utf8 COMMENT=\"数据库数据初始化日志\"";
    //保存需要canal同步的表
    public final static String DB_CANAL_TABLE = "db_canal_table";
    private final static String DB_CANAL_TABLE_DDL = "CREATE TABLE "+DB_CANAL_TABLE+"("
            + "id bigint unsigned NOT NULL PRIMARY KEY AUTO_INCREMENT COMMENT '编号', "
            + "tablename varchar(128) NOT NULL COMMENT '表名',"
            + "pkcolumn varchar(128) NOT NULL COMMENT '主键列',"
            + "toes bit(1) NOT NULL COMMENT '是否同步到es',"
            + "tokafka bit(1) NOT NULL COMMENT '是否同步到kafka',"
            + "inited bit(1) NOT NULL COMMENT '是否已初始化',"
            + "UNIQUE (tablename)"
            + ")DEFAULT CHARSET=utf8 COMMENT=\"保存需要同步到canal的表的基本信息\"";


    private JdbcTemplate jt = null;
    private DbAdapter da;
    public DbTracker(JdbcTemplate jt, DbAdapter da) {
        this.jt = jt;
        this.da = da;
    }
    public void init() {
        String[] dbs = new String[] {
                DB_CANAL_TABLE, DB_CANAL_TABLE_DDL,
                DB_INIT_TABLE,DB_INIT_TABLE_DDL
        };
        for(int i=0; i<dbs.length; i+=2) {
            createTableIfNotExists(dbs[i], dbs[i+1]);
        }
    }
    private void createTableIfNotExists(String table, String sql) {
        if(!da.isInDb(table)) {
            try {
                jt.execute(sql);
            } catch (Exception e) {
                throw new RepositoryException("建立数据表[" + table + "]失败", e);
            }
        }
    }
    private static String wrapName(String name){
        return "`" + name + "`";
    }

    /**
     * 形成表初始化的ddl
     * @param table
     * @return
     */
    public String generateTableCreateDdl(DbTable table) {

        StringBuffer sb = new StringBuffer("CREATE TABLE IF NOT EXISTS " + wrapName(table.getName()) + "(");
        StringBuffer colsb = new StringBuffer();
        String idcolstr = "";
        for(int i=0,len=table.getColumns().size(); i<len; i++){
            DbTableColumn col = table.getColumns().get(i);
            if(col.isIdentity()) {
                idcolstr = idcolstr + "," + wrapName(col.getName());
            }
            colsb.append(wrapName(col.getName()) + " " + generateColumnAppend(col, true) + ",");
        }
        if(idcolstr.isEmpty()){
            throw new RuntimeException("必需为表"+table.getName()+"指定主键列");
        }
        colsb.append("PRIMARY KEY(" + idcolstr.substring(1) + "),");
        sb.append(colsb.substring(0, colsb.length()-1));
        sb.append(")DEFAULT CHARSET=utf8 COMMENT='"+table.getComment()+"'");
        return sb.toString();
    }


    /**
     * 新加列
     * @param table
     * @param col
     * @return
     */
    public String generateColumnCreateDdl(DbTable table, DbTableColumn col) {
        return "ALTER TABLE " + wrapName(table.getName()) + " ADD " + wrapName(col.getName())
                + " " + generateColumnAppend(col, true);
    }
    /**
     * 获取列的后缀
     * @param c
     * @param withdefault
     * @return
     */
    private String generateColumnAppend(DbTableColumn c, boolean withdefault) {
        StringBuffer sb = new StringBuffer();
        //添加类型
        sb.append(c.getColumnType() + " ");
        //添加是否充许为空
        if(!c.isNullable()) {
            sb.append("NOT NULL ");
        }else {
            sb.append("NULL ");
        }
        //是否需要添加默认值
        if(withdefault) {
            if(c.getDefaultValue() != null && !c.getDefaultValue().isEmpty()) {
                sb.append("DEFAULT " + c.getDefaultValue() + " ");
            }
        }
        //考虑附加定义
        if(c.getExtra() != null) {
            sb.append(c.getExtra() + " ");
        }
        //添加注解
        sb.append("COMMENT '" + c.getComment() + "'");
        return sb.toString();
    }
    public String generateColumnModifyDdl(DbTable table, DbTableColumn col) {
        return "ALTER TABLE " + wrapName(table.getName()) + " MODIFY " + wrapName(col.getName()) + " "
                + generateColumnAppend(col, true);
    }
    public boolean isDataInited(String key, int version) {
        List<Map<String, Object>> ls =
                jt.queryForList("select id from "+DB_INIT_TABLE+" where init_key = ? and version = ?", key, version+"");
        return !ls.isEmpty();
    }
    public void generateUpdateDataInitedVersion(String key, int version, List<String> sqls) {
        if(jt.queryForList("select id from " + DB_INIT_TABLE + " where init_key = ?", key).isEmpty()){
            sqls.add("insert into "+DB_INIT_TABLE+"(init_key, version, init_time) values('"+key+"',"+version+",'"+new SimpleDateFormat("yyyy-MM-dd HH:mm:ss").format(new Date())+"')");
        }else{
            sqls.add("update "+DB_INIT_TABLE+" set version="+version+", init_time='"+new SimpleDateFormat("yyyy-MM-dd HH:mm:ss").format(new Date())+"' where init_key='" + key + "'");
        }
    }
}